﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Core.Objects;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Validation;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Web;
using System.Data.SqlClient;

namespace PlatWL.Data
{

    /// <summary>
    /// 公共接口
    /// </summary>
    public class BaseEFDao<T> : IBaseDao<T> where T : class, new()   //限制T为class
    {
        private DbContext context = new PlatWLEntities();  //DbContextFactory.GetCurrent();//获取EF上下文

        /// <summary>
        /// 获取记录数
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public virtual int GetCount(Func<T, bool> exp)
        {
            return context.Set<T>().Where(exp).Count();
        }

        public virtual IEnumerable<T> GetEntities(Expression<Func<T, bool>> exp = null)
        {
            if (exp == null)
            {
                return context.Set<T>().AsNoTracking().ToList();
            }
            else
            {
                return context.Set<T>().Where(exp).AsNoTracking().ToList();
            }
        }
        /// <summary>
        /// 根据条件查找
        /// </summary>
        /// <param name="exp">lambda查询条件where</param>
        /// <returns></returns>
        public virtual T GetEntity(Expression<Func<T, bool>> exp)
        {
            return context.Set<T>().Where(exp).SingleOrDefault();
        }

        public virtual T GetEntityFirst(Expression<Func<T, bool>> exp)
        {
            return context.Set<T>().Where(exp).FirstOrDefault();
        }


        public virtual IQueryable<T> GetEntities(Func<T, bool> whereLambda, Func<T, dynamic> orderName, string sortOrder, int pageIndex, int pageSize, out int totalCount)
        {
            var tmp = context.Set<T>().AsQueryable();
            if (whereLambda != null)
            {
                tmp = tmp.Where<T>(whereLambda).AsQueryable();
            }
            totalCount = tmp.Count();

            if (sortOrder == "asc")
            {
                return tmp.OrderBy(orderName).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList().AsQueryable();
            }
            else
            {
                return tmp.OrderByDescending(orderName).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList().AsQueryable();

            }

        }

        /// <summary>
        /// 插入Entity
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public virtual bool Insert(T entity)
        {
            context.Set<T>().Add(entity);
            return context.SaveChanges() > 0;
        }

        /// <summary>
        /// 同时插入多个实体。
        /// </summary>
        /// <param name="entities"></param>
        /// <returns></returns>
        public virtual bool Insert(IEnumerable<T> entities)
        {
            foreach (var entity in entities)
            {
                context.Set<T>().Add(entity);
            }
            return context.SaveChanges() > 0;
        }

        /// <summary>
        /// 更新Entity(注意这里使用的傻瓜式更新,可能性能略低)
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public virtual bool Update(T entity)
        {
            context.Set<T>().Attach(entity);
            context.Entry(entity).State = System.Data.Entity.EntityState.Modified;
            return context.SaveChanges() > 0;
        }

        /// <summary>
        /// 删除Entity
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual bool Delete(T entity)
        {
            if (entity != null)
            {
                context.Set<T>().Attach(entity);
                context.Entry(entity).State = System.Data.Entity.EntityState.Deleted;
                return context.SaveChanges() > 0;
            }
            return false;
        }
        /// <summary>
        /// 批量删除Entity
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual bool Delete(Func<T, bool> whereLambda)
        {
            var tmp = context.Set<T>().Where(whereLambda);
            foreach (var item in tmp)
            {
                context.Entry(item).State = System.Data.Entity.EntityState.Deleted;
            }
            return context.SaveChanges() > 0;
        }



        /// <summary>
        /// 根据条件查找
        /// </summary>
        /// <param name="CommandText">Sql语句</param>
        /// <param name="objParams">可变参数</param>
        /// <returns></returns>
        public virtual DataTable GetDTByCommand(string CommandText)
        {
            return SqlQueryForDataTatable(CommandText);
        }

        /// <summary>
        /// EF SQL 语句返回 dataTable
        /// </summary>
        /// <param name="db"></param>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable SqlQueryForDataTatable(string sql)
        {
            DataTable ds = new DataTable();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = context.Database.Connection.ConnectionString;
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
            }
            catch { }
            finally { conn.Close(); }
            return ds;
        }

        public DataSet SqlQueryForDataSet(string sql)
        {
            DataSet ds = new DataSet();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = context.Database.Connection.ConnectionString;
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
            }
            catch { }
            finally { conn.Close(); }
            return ds;
        }

        //执行SQL语句
        public void ExsSql(string sql)
        {

            string connectionString = context.Database.Connection.ConnectionString;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connectionString;
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
            catch { }
            finally { conn.Close(); }
        }

        public string ExsSclarSql(string sql)
        {

            string connectionString = context.Database.Connection.ConnectionString;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connectionString;
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                var result = cmd.ExecuteScalar();

                return result == null ? "" : result.ToString();
            }
            catch { }
            finally { conn.Close(); }
            return "";
        }

        /// <summary>
        /// 数据分页
        /// </summary>
        /// <param name="viewName">表名</param>
        /// <param name="fieldName">字段</param>
        /// <param name="pageSize">默认20</param>
        /// <param name="pageNo">页数</param>
        /// <param name="orderString">排序</param>
        /// <param name="whereString">可选</param>
        /// <param name="recordTotal">总数</param>
        /// <returns></returns>
        public DataTable GetDataPager(string viewName, string fieldName, int pageSize, int pageNo, string orderString, string whereString, ref int recordTotal)
        {

            DataSet ds = new DataSet();
            string connectionString = context.Database.Connection.ConnectionString;

            SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            conn.ConnectionString = connectionString;
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "usp_DataPager";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] paras = new SqlParameter[7];
                paras[0] = new SqlParameter("viewName", viewName);
                paras[1] = new SqlParameter("fieldName", fieldName);
                paras[2] = new SqlParameter("pageSize", pageSize);
                paras[3] = new SqlParameter("pageNo", pageNo);
                paras[4] = new SqlParameter("orderString", orderString);
                if (string.IsNullOrWhiteSpace(whereString))
                {
                    whereString = " 1=1 ";
                }
                paras[5] = new SqlParameter("whereString", whereString);
                paras[5].Size = int.MaxValue;
                paras[6] = new SqlParameter("recordTotal", recordTotal);
                paras[6].Direction = ParameterDirection.Output;
                cmd.Parameters.AddRange(paras);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
                recordTotal = int.Parse(paras[6].Value == null ? "0" : paras[6].Value.ToString());
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally { conn.Close(); }

        }

    }


}